import express from "express";
const article = express.Router();
import { dbContext } from "../../database/initialize.js";

// 根据id获取文章详情
article.get("/article/detail/:id", (req, res) => {
    try {
        // 验证数据库连接
        if (!dbContext || !dbContext.db) {
            return res.status(500).json({success: false, data: null, message: "数据库连接未初始化"});
        }

        const { id } = req.params;
        if(!id || typeof id !== 'string') {
            return res.status(400).json({success: false, data: null, message: "文章ID格式不正确"});
        };

        const validTypes = req.query.validTypes ? req.query.validTypes.split(",").map(Number) : null;
        
        // 查询当前文章
        let currentSql = "SELECT * FROM fps_article WHERE ARTICLE_ID = ?";
        let currentParams = [id];
        
        if(validTypes && validTypes.length > 0) {
            currentSql += ` AND ARTICLE_TYPE IN (${validTypes.map(() => "?").join(",")})`;
            currentParams.push(...validTypes);
        };
        
        currentSql += " LIMIT 1";
        const stmt = dbContext.db.prepare(currentSql);
        stmt.bind(currentParams);
        const articleData = stmt.step() ? stmt.getAsObject() : null;
        stmt.free();
        
        if(!articleData) {
            return res.status(404).json({success: false, data: null, message: validTypes ? "文章不存在或不符合类型要求" : "文章不存在"});
        };

        // 查询相邻文章（使用创建时间排序）
        const adjacentArticles = [];
        
        // 上一篇（按创建时间倒序）
        let prevSql = `
            SELECT * FROM fps_article 
            WHERE CREATED_AT < (SELECT CREATED_AT FROM fps_article WHERE ARTICLE_ID = ?)
            ${validTypes && validTypes.length > 0 ? `AND ARTICLE_TYPE IN (${validTypes.map(() => '?').join(',')})` : ''}
            ORDER BY CREATED_AT DESC LIMIT 1
        `;
        let prevParams = [id];
        if (validTypes && validTypes.length > 0) prevParams.push(...validTypes);
        
        const prevStmt = dbContext.db.prepare(prevSql);
        prevStmt.bind(prevParams);
        const prevArticle = prevStmt.step() ? prevStmt.getAsObject() : null;
        prevStmt.free();
        adjacentArticles.push(prevArticle);

        // 下一篇（按创建时间正序）
        let nextSql = `
            SELECT * FROM fps_article 
            WHERE CREATED_AT > (SELECT CREATED_AT FROM fps_article WHERE ARTICLE_ID = ?)
            ${validTypes && validTypes.length > 0 ? `AND ARTICLE_TYPE IN (${validTypes.map(() => '?').join(',')})` : ''}
            ORDER BY CREATED_AT ASC LIMIT 1
        `;
        let nextParams = [id];
        if (validTypes && validTypes.length > 0) nextParams.push(...validTypes);
        
        const nextStmt = dbContext.db.prepare(nextSql);
        nextStmt.bind(nextParams);
        const nextArticle = nextStmt.step() ? nextStmt.getAsObject() : null;
        nextStmt.free();
        adjacentArticles.push(nextArticle);

        res.status(200).json({
            success: true, 
            data: {
                current: articleData, 
                previous: adjacentArticles[0],
                next: adjacentArticles[1]
            }, 
            message: "获取文章详情成功"
        });
    } catch (error) {
        console.error("/article/detail/:id 请求时发生错误：", error);
        console.error("请求参数：", req.params);
        res.status(500).json({
            success: false, 
            data: null, 
            message: "获取文章详情失败: " + error.message
        });
    }
});

// 获取文章列表
article.post("/article/list", async (req, res) => {
    let countStmt, stmt;
    try {
        const { pages = {}, querys = {} } = req.body;
        const page = parseInt(pages.page) || 1;
        const pageSize = parseInt(pages.pageSize) || 10;
        const offset = (page - 1) * pageSize;
        
        let query = "SELECT * FROM fps_article";
        let countQuery = "SELECT COUNT(*) as total FROM fps_article";
        const params = [];
        const whereClauses = [];
        
        if(Object.keys(querys).length > 0) {
            const finalQuery = {
                ...querys
            };
            for(const [key, value] of Object.entries(finalQuery)) {
                if(value !== null && value !== undefined) {
                    if(key === "like" && value) {
                        whereClauses.push(`ARTICLE_TITLE LIKE ?`);
                        params.push(`%${value}%`);
                    } else if(key === "ARTICLE_TYPE" && Array.isArray(value) && value.length > 0) {
                        // 处理ARTICLE_TYPE数组
                        const placeholders = value.map(() => "?").join(",");
                        whereClauses.push(`${key} IN (${placeholders})`);
                        params.push(...value);
                    } else if(value !== null) {
                        whereClauses.push(`${key} = ?`);
                        params.push(value);
                    }
                }
            };
            
            if(whereClauses.length > 0) {
                query += " WHERE " + whereClauses.join(" AND ");
                countQuery += " WHERE " + whereClauses.join(" AND ");
            }
        };
        
        query += ` ORDER BY CREATED_AT DESC LIMIT ${pageSize} OFFSET ${offset}`;
        
        // 执行计数查询
        countStmt = dbContext.db.prepare(countQuery);
        countStmt.bind(params);
        const totalResult = countStmt.step() ? countStmt.getAsObject() : { total: 0 };
        
        // 执行数据查询
        stmt = dbContext.db.prepare(query);
        stmt.bind(params);
        const articles = [];
        while (stmt.step()) {
            articles.push(stmt.getAsObject());
        };
        const totalPage = Math.ceil(Number(totalResult.total) / Number(pageSize)) || 0;
        return res.status(200).json({success: true, data: {list: articles, total: totalResult.total, page: page, pageSize: pageSize, totalPage: totalPage}, message: "获取文章列表成功"});
        
    } catch (error) {
        console.error("/article/list 请求时发生错误：", error);
        return res.status(500).json({
            success: false, 
            data: null, 
            message: "获取文章列表失败: " + error.message
        });
    } finally {
        // 确保释放所有语句句柄
        if (countStmt) countStmt.free();
        if (stmt) stmt.free();
    }
});

// 查询文章

export default article;